
import os,sys
sys.path.append(os.getcwd())
from src.utils.db_processor import presto_processor,mysql_prediction_processor
from src.diy_pc.diy_pc_config import CATEGORY_PROPERTY_VALUE_PRICE_SQL, TEMPLATE_LEVEL_RATE_SQL, PRODUCT_BASE_PRICE_SQL,SKU_PROPERTY_SQL,PRODUCT_LEVEL_SQL,PRODUCT_LEVEL_NAME_SQL,CATEGORY_PRODUCT_PRICE_SQL
import pandas
from src.utils.util import get_today, format_date_string
from src.utils.feishu_message import feishu_messager
import datetime

#显示所有列
pandas.set_option('display.max_columns', None)
#显示所有行presto_processor
pandas.set_option('display.max_rows', None)
#设置value的显示长度为100，默认为50
pandas.set_option('max_colwidth',100)

class Diy_Pc_Model:
    def __init__(self, model_date=None):
        if model_date is None:
            self.model_date = get_today() + datetime.timedelta(days=1)
        else:
            self.model_date = model_date

        self.model_date = format_date_string(self.model_date)

    def get_data(self):

        self.product_base_price_data = presto_processor.load_sql(PRODUCT_BASE_PRICE_SQL)
        product_base_price_data_zzj = presto_processor.load_sql(CATEGORY_PRODUCT_PRICE_SQL)

        all_zzj_product_ids = list(set(product_base_price_data_zzj['product_id']))
        zzj_product_ids = []
        for i in all_zzj_product_ids:
            if i not in self.product_base_price_data['product_id']:
                zzj_product_ids.append(i)
        product_base_price_data_zzj = product_base_price_data_zzj[product_base_price_data_zzj['product_id'].isin(zzj_product_ids)]

        self.product_base_price_data = pandas.concat([self.product_base_price_data, product_base_price_data_zzj])
        self.product_base_price_data = self.product_base_price_data.drop_duplicates(subset=['product_id'],keep='first')

        #如果没有组装机的型号，写入一条组装机型号数据
        # if len(self.product_base_price_data[self.product_base_price_data['product_id']==108563])==0:
        #     self.product_base_price_data.loc[len(self.product_base_price_data)] = {'product_id': 108563, 'product_base_price': 0}

        self.category_property_value_price_data = presto_processor.load_sql(CATEGORY_PROPERTY_VALUE_PRICE_SQL)

        self.template_level_rate_data = presto_processor.load_sql(TEMPLATE_LEVEL_RATE_SQL)

        self.level_name_data = presto_processor.load_sql(PRODUCT_LEVEL_NAME_SQL)

        product_ids = list(set(self.product_base_price_data['product_id'].astype(str)))
        product_ids_str = ','.join(product_ids)
        self.sku_property_data = presto_processor.load_sql(SKU_PROPERTY_SQL.format(product_ids_str))

        print('监控 c2b型号配置表型号数量为{}, sku 属性表中的型号数量为{}'.format(len(product_ids), len(list(set(self.sku_property_data.product_id)))))

        self.product_level_data = presto_processor.load_sql(PRODUCT_LEVEL_SQL.format(product_ids_str))

        print('get_data over')


    def column_to_list(self, x):
        valies = [x['property_name_name'], x['property_name_id'], x['product_property_value_name'], x['product_property_value_id'], x['property_value_back_price'], x['product_base_price']]

        columns = str(list(valies[0]))

        for value in valies[1:]:
            value_temp = str(list(value))
            columns=columns + ','+value_temp

        return columns

    def property_add_product_price(self, x):
        '''
        型号价格加上 sku属性价格
        '''
        #如果有属性值的价格为0，输出价格为0元。（对固态硬盘和机械硬盘特殊处理，如果固态硬盘和机械硬盘同时为0，价格返回0）
        property_price_lis = list(x['property_value_back_price'])
        for p in property_price_lis:
            if p == 0:
                return 0

        #计算价格
        property_price = sum(x['property_value_back_price'])
        product_price = list(x['product_base_price'])[0]
        property_add_product_price = property_price+product_price
        return property_add_product_price



    def get_sku_price(self, product_base_price_data, category_property_value_price_data, sku_property_data):
        '''
        获取property_product价格
        :param product_base_price_data:
        :param category_property_value_price_data:
        :param sku_property_data:
        :return:
        '''
        sku_price_data = pandas.merge(sku_property_data, category_property_value_price_data, left_on=['product_category_id','property_name_id','product_property_value_id'],right_on=['product_category_id','property_name_id','property_value_back_id'],how='left')
        # print(sku_price_data[sku_price_data['property_value_back_price'].isnull()])
        # sku_price_data = sku_price_data.fillna(0)

        # sku_price_data = pandas.merge(sku_price_data, product_base_price_data, left_on=['product_id'],right_on=['product_id'],how='inner')
        sku_price_data = pandas.merge(product_base_price_data, sku_price_data, left_on=['product_id'],right_on=['product_id'],how='left')
        sku_price_data = sku_price_data.loc[sku_price_data['property_name_name'].notnull()]
        sku_price_data['property_name_name'] = sku_price_data['property_name_name'].astype(str)
        sku_price_data['property_name_name'] = sku_price_data['property_name_name'].fillna('')
        sku_price_data = sku_price_data.fillna(0)

        sku_price_data.sort_values('property_name_name',inplace=True)


        #获取sku对应的价格
        sku_property_price_data = sku_price_data.groupby(['product_sku_id'])['property_value_back_price','product_base_price','property_name_id'].apply(lambda x:self.property_add_product_price(x))
        sku_property_price_data = sku_property_price_data.reset_index()
        sku_property_price_data.rename(columns={0:'sku_product_price'},inplace=True)
        # print('sku_property_price_data.head() :',sku_property_price_data.head())
        # sku_price_data.to_csv('sku_price_data1.csv', index=False, encoding='utf-8-sig')

        # #获取sku对应的其他数据
        property_name_name_data = sku_price_data.groupby(['product_sku_id']).apply(lambda x:self.column_to_list(x))
        property_name_name_data = property_name_name_data.reset_index()
        property_name_name_data.rename(columns={0:'sku_price_info'},inplace=True)
        # print('property_name_name_data 11 :',property_name_name_data.head())

        #sku基础数据
        sku_price_data = sku_price_data[['product_brand_id','product_brand_name','product_sku_id', 'product_sku_name', 'product_id', 'product_name', 'product_category_id','product_category_name', 'product_base_price']]
        sku_price_data = sku_price_data.drop_duplicates()


        sku_price_data = pandas.merge(sku_price_data, sku_property_price_data, left_on=['product_sku_id'],right_on=['product_sku_id'],how='left')
        sku_price_data = pandas.merge(sku_price_data, property_name_name_data, left_on=['product_sku_id'],right_on=['product_sku_id'],how='left')
        return sku_price_data


    def get_sku_level_price(self, sku_price_data,template_level_rate_data,product_level_data):
        '''
        property_product价格乘以level价格
        :param product_base_price_data:
        :param category_property_value_price_data:
        :param sku_property_data:
        :return:
        '''

        sku_price_data = pandas.merge(sku_price_data, product_level_data, left_on=['product_id'],right_on=['product_id'],how='left')
        sku_price_data = pandas.merge(sku_price_data, template_level_rate_data, left_on=['product_level_template_id'],right_on=['product_level_template_id'],how='left')
        sku_price_data = pandas.merge(sku_price_data, self.level_name_data, left_on=['product_level_id'],right_on=['product_level_id'],how='left')

        # sku_price_data.to_csv('sku_price_data.csv', index=False, encoding='utf-8-sig')
        sku_price_data['product_level_rate'] = sku_price_data['product_level_rate'].astype(float)
        sku_price_data['sku_product_level_price'] = sku_price_data['sku_product_price']*sku_price_data['product_level_rate']

        sku_price_data['sku_product_level_price_old'] = sku_price_data['sku_product_level_price']
        sku_price_data.loc[(sku_price_data['sku_product_level_price'] < 5)&(sku_price_data['sku_product_level_price'] >0), 'sku_product_level_price'] = 5
        # print(sku_price_data.head())
        # sku_price_data.to_csv('sku_price_data.csv', index=False, encoding='utf-8-sig')
        sku_price_data = sku_price_data[~(sku_price_data['sku_product_level_price'].isna())]

        return sku_price_data

    def save_intermediate_result_data(self,data):
        '''
        存储中间结果
        :param data:
        :return:
        '''

        data = data[data['sku_product_level_price']>0]

        data['date'] = self.model_date

        model_date_dt = datetime.datetime.strptime(self.model_date, '%Y-%m-%d')
        model_date_dt_before_7days = model_date_dt - datetime.timedelta(days=7)
        model_date_dt_before_7days = model_date_dt_before_7days.strftime('%Y-%m-%d')

        delete_history_sql = '''
        DELETE FROM price_prediction_c2b_diy_pc_info where date <= '{}' 
        '''.format(model_date_dt_before_7days)
        mysql_prediction_processor.execute_sql(delete_history_sql)

        delete_sql = '''
        DELETE FROM price_prediction_c2b_diy_pc_info
        where date = '{}' and product_category_id in (91, 93, 354)
        '''.format(self.model_date)
        mysql_prediction_processor.execute_sql(delete_sql)

        insert_sql = """
        INSERT INTO price_prediction_c2b_diy_pc_info(product_brand_id,product_id,product_category_id,product_sku_id,product_sku_name,product_level_template_id,product_level_id,product_base_price,sku_product_price,product_level_rate,sku_price_info,sku_product_level_price,date)
        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        mysql_prediction_processor.execute_insert_sql(insert_sql,
                                                      data[
                                                          ['product_brand_id','product_id', 'product_category_id',
                                                           'product_sku_id', 'product_sku_name',
                                                           'product_level_template_id', 'product_level_id', 'product_base_price',
                                                           'sku_product_price','product_level_rate','sku_price_info',
                                                           'sku_product_level_price','date']
                                                      ].to_records(index=False).tolist())


    def save_complement_data(self, data):
        '''
        存储最终结果
        :param data:
        :return:
        '''
        data['date'] = self.model_date
        data = data[data['sku_product_level_price']>0]

        # print(data[
        #           ['date', 'product_sku_id', 'product_sku_name',
        #            'product_level_id', 'product_level_name',
        #            'product_id', 'product_name',
        #            'product_category_id', 'product_category_name',
        #            'product_brand_name',
        #            'sku_product_level_price', 'product_brand_id']
        #       ].dtypes)


        # 如果已有当日数据,删除当日数据
        delete_sql = '''
        DELETE FROM price_prediction_train
        where product_category_id in (91, 93, 354)
        '''.format(self.model_date)
        mysql_prediction_processor.execute_sql(delete_sql)


        insert_sql = """
        INSERT INTO price_prediction_train(date,product_sku_key,product_sku_name,product_level_key,product_level_name,product_key,product_name,product_category_id,product_category_name,product_brand_name,forecast_reference_price,product_brand_id)
        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        mysql_prediction_processor.execute_insert_sql(insert_sql,
                                                      data[
                                                          ['date', 'product_sku_id', 'product_sku_name',
                                                           'product_level_id', 'product_level_name',
                                                           'product_id', 'product_name',
                                                           'product_category_id','product_category_name',
                                                           'product_brand_name',
                                                           'sku_product_level_price', 'product_brand_id']
                                                      ].to_records(index=False).tolist())


    def run(self):

        self.get_data()
        sku_price_data = self.get_sku_price(self.product_base_price_data, self.category_property_value_price_data, self.sku_property_data)
        sku_price_data = self.get_sku_level_price(sku_price_data, self.template_level_rate_data, self.product_level_data)
        self.save_intermediate_result_data(sku_price_data)
        self.save_complement_data(sku_price_data)


if __name__ == '__main__':
    try:
        dpm = Diy_Pc_Model()
        dpm.run()
    except Exception as e:
        feishu_messager.send_message('一体机处理价格失败：{}'.format(e))
        raise TypeError('一体机处理价格失败:') from e
        exit(1)